/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package econutri.servicos.sujestao;

import econutri.persistencia.entidades.Alimento;
import econutri.persistencia.entidades.Aversao;
import econutri.persistencia.entidades.Preferencia;
import econutri.persistencia.entidades.Recomendacao;
import econutri.persistencia.entidades.RecordatorioItem;
import econutri.util.Constants;
import econutri.util.Utils;
import java.io.IOException;
import java.io.PrintWriter;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import lomtools.persistence.GenericDAO;

/**
 *
 * @author Leonardo Oliveira Moreira
 */
public class ProcessarSelecaoAlimentoXMLServlet extends HttpServlet {
   
    /** 
     * Processes requests for both HTTP <code>GET</code> and <code>POST</code> methods.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    protected void processRequest(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        response.setContentType("application/xml;charset=ISO-8859-1");
        String xml = "";
        String pacienteCodigo = request.getParameter("pacienteCodigo");

        List<Aversao> aversaoList = new ArrayList<Aversao>();
        List<Preferencia> preferenciaList = new ArrayList<Preferencia>();
        List<RecordatorioItem> recordatorioList = new ArrayList<RecordatorioItem>();
        List<Recomendacao> recomendacaoList = new ArrayList<Recomendacao>();
        List<Alimento> sugestaoList = new ArrayList<Alimento>();
        String sql = "";
        try {
            GenericDAO dao = new GenericDAO();
            sql = "SELECT a.alimentocodigo, a.pacientecodigo FROM aversao a WHERE a.pacientecodigo = " + pacienteCodigo + "";
            ResultSet rsAversao = dao.executeQuery(sql);
            while (rsAversao != null && rsAversao.next()) {
                Aversao a = new Aversao();
                a.setAlimentoCodigo(rsAversao.getLong("alimentocodigo"));
                a.setPacienteCodigo(rsAversao.getLong("pacientecodigo"));
                aversaoList.add(a);
            }
            sql = "SELECT a.alimentocodigo, a.pacientecodigo, a.nivel FROM preferencia a WHERE a.pacientecodigo = " + pacienteCodigo + "";
            ResultSet rsPreferencia = dao.executeQuery(sql);
            while (rsPreferencia != null && rsPreferencia.next()) {
                Preferencia p = new Preferencia();
                p.setAlimentoCodigo(rsPreferencia.getLong("alimentocodigo"));
                p.setPacienteCodigo(rsPreferencia.getLong("pacientecodigo"));
                p.setNivel(rsPreferencia.getLong("nivel"));
                preferenciaList.add(p);
            }
            sql = "SELECT " +
                    "refeicaocodigo, alimentocodigo, pacientecodigo, quantidade, data " +
                  "FROM recordatorioitem " +
                  "WHERE pacientecodigo = " + pacienteCodigo + " " +
                  "AND " +
                  "data = (SELECT data FROM recordatorioitem WHERE pacientecodigo = " + pacienteCodigo + " ORDER BY data DESC LIMIT 1)";
            ResultSet rsRecordatorio = dao.executeQuery(sql);
            String dataRecordatorio = "";
            while (rsRecordatorio != null && rsRecordatorio.next()) {
                RecordatorioItem r = new RecordatorioItem();
                r.setAlimentoCodigo(rsRecordatorio.getLong("alimentocodigo"));
                r.setPacienteCodigo(rsRecordatorio.getLong("pacientecodigo"));
                r.setRefeicaoCodigo(rsRecordatorio.getLong("refeicaocodigo"));
                r.setQuantidade(rsRecordatorio.getLong("quantidade"));
                r.setData(rsRecordatorio.getDate("data"));
                dataRecordatorio = Utils.converterDataSQL(Utils.treatInterface(r.getData()));
                recordatorioList.add(r);
            }
            sql = "SELECT " +
                    "refeicaocodigo, alimentocodigo, pacientecodigo, tipocodigo " +
                  "FROM recomendacao " +
                  "WHERE pacientecodigo = " + pacienteCodigo;
            ResultSet rsRecomendacao = dao.executeQuery(sql);
            while (rsRecomendacao != null && rsRecomendacao.next()) {
                Recomendacao r = new Recomendacao();
                r.setAlimentoCodigo(rsRecomendacao.getLong("alimentocodigo"));
                r.setPacienteCodigo(rsRecomendacao.getLong("pacientecodigo"));
                r.setRefeicaoCodigo(rsRecomendacao.getLong("refeicaocodigo"));
                r.setTipoCodigo(rsRecomendacao.getLong("tipocodigo"));
                recomendacaoList.add(r);
            }
            sql =   "SELECT al.codigo, al.descricao, al.quantidade, al.carboidrato, al.proteina, al.lipidio, al.calcio, al.fosforo, al.ferro, al.sodio, al.vitaminaa, al.vitaminab1, al.vitaminab2, al.niacina, al.vitaminac, al.fibra, al.colesterol, al.preco " +
                    "FROM alimento al " + 
                    "WHERE (al.codigo " + 
                    "IN " + 
                    "(" + 
                    "((SELECT pref.alimentocodigo FROM preferencia pref WHERE pref.pacientecodigo = " + pacienteCodigo + ") UNION " + 
                    "(SELECT rcdt.alimentocodigo FROM recordatorioitem rcdt WHERE rcdt.pacientecodigo = " + pacienteCodigo + " AND rcdt.data = '" + dataRecordatorio + "') UNION " + 
                    "(SELECT rcmd.alimentocodigo FROM recomendacao rcmd WHERE rcmd.pacientecodigo = " + pacienteCodigo + " and rcmd.tipocodigo = " + Constants.RECOMENDACAO_INCLUSAO + ")) " +
                    "EXCEPT " + 
                    "((SELECT av.alimentocodigo FROM aversao av WHERE av.pacientecodigo = " + pacienteCodigo + ") UNION " + 
                    "(SELECT rcmd.alimentocodigo FROM recomendacao rcmd WHERE rcmd.pacientecodigo = " + pacienteCodigo + " and rcmd.tipocodigo = " + Constants.RECOMENDACAO_INIBICAO + " GROUP BY rcmd.alimentocodigo HAVING count(*) = (SELECT count(*) FROM refeicao)))" +
                    ")" + 
                    ")";
            ResultSet rsSugestao = dao.executeQuery(sql);
            while (rsSugestao != null && rsSugestao.next()) {

                Alimento a = new Alimento();
                
                a.setCodigo(rsSugestao.getLong("codigo"));
                a.setDescricao(rsSugestao.getString("descricao"));
                a.setQuantidade(rsSugestao.getDouble("quantidade")/100);

                a.setCarboidrato(Utils.formatDoubleValue(rsSugestao.getDouble("carboidrato")/100));
                a.setProteina(Utils.formatDoubleValue(rsSugestao.getDouble("proteina")/100));
                a.setLipidio(Utils.formatDoubleValue(rsSugestao.getDouble("lipidio")/100));

                a.setCalcio(Utils.formatDoubleValue(rsSugestao.getDouble("calcio")/100));
                a.setFosforo(Utils.formatDoubleValue(rsSugestao.getDouble("fosforo")/100));
                a.setFerro(Utils.formatDoubleValue(rsSugestao.getDouble("ferro")/100));
                a.setSodio(Utils.formatDoubleValue(rsSugestao.getDouble("sodio")/100));
                a.setVitaminaA(Utils.formatDoubleValue(rsSugestao.getDouble("vitaminaa")/100));
                a.setVitaminaB1(Utils.formatDoubleValue(rsSugestao.getDouble("vitaminab1")/100));
                a.setVitaminaB2(Utils.formatDoubleValue(rsSugestao.getDouble("vitaminab2")/100));
                a.setNiacina(Utils.formatDoubleValue(rsSugestao.getDouble("niacina")/100));
                a.setVitaminaC(Utils.formatDoubleValue(rsSugestao.getDouble("vitaminac")/100));
                a.setFibra(Utils.formatDoubleValue(rsSugestao.getDouble("fibra")/100));
                a.setColesterol(Utils.formatDoubleValue(rsSugestao.getDouble("colesterol")/100));
                
                a.setPreco(Utils.formatDoubleValue(rsSugestao.getDouble("preco")/100));

                sugestaoList.add(a);
            }
            dao.close();
        } catch (SQLException ex) {
            Logger.getLogger(ProcessarSelecaoAlimentoXMLServlet.class.getName()).log(Level.SEVERE, null, ex);
        }
        for (Aversao a : aversaoList) {

        }
        for (Preferencia p : preferenciaList) {

        }
        for (RecordatorioItem r : recordatorioList) {

        }
        for (Recomendacao r : recomendacaoList) {

        }
        xml += "<result>";
            xml += "<paciente>";
                xml += "<codigo>" + pacienteCodigo + "</codigo>";
            xml += "</paciente>";
            xml += "<alimentos>";
            for (int i = 0; i < sugestaoList.size(); i++) {
                Alimento a = sugestaoList.get(i);
                xml += "<alimento>";
                    xml += "<variavel>X" + (i + 1) + "</variavel>";
                    xml += "<codigo>" + a.getCodigo() + "</codigo>";
                    xml += "<descricao>" + a.getDescricao() + "</descricao>";
                    xml += "<quantidade>" + a.getQuantidade() + "</quantidade>";

                    xml += "<carboidrato>" + a.getCarboidrato() + "</carboidrato>";
                    xml += "<proteina>" + a.getProteina() + "</proteina>";
                    xml += "<lipidio>" + a.getLipidio() + "</lipidio>";

                    xml += "<calcio>" + a.getCalcio() + "</calcio>";
                    xml += "<fosforo>" + a.getFosforo() + "</fosforo>";
                    xml += "<ferro>" + a.getFerro() + "</ferro>";

                    xml += "<sodio>" + a.getSodio() + "</sodio>";
                    xml += "<vitaminaa>" + a.getVitaminaA() + "</vitaminaa>";
                    xml += "<vitaminab1>" + a.getVitaminaB1() + "</vitaminab1>";

                    xml += "<vitaminab2>" + a.getVitaminaB2() + "</vitaminab2>";
                    xml += "<niacina>" + a.getNiacina() + "</niacina>";
                    xml += "<vitaminac>" + a.getVitaminaC() + "</vitaminac>";
                    
                    xml += "<fibra>" + a.getFibra() + "</fibra>";
                    xml += "<colesterol>" + a.getColesterol() + "</colesterol>";

                    xml += "<preco>" + a.getPreco() + "</preco>";
                xml += "</alimento>";
            }
            xml += "</alimentos>";
        xml += "</result>";
        PrintWriter out = response.getWriter();
        try {
            out.println(xml);
        } finally {
            out.close();
        }
    }
    
    // <editor-fold defaultstate="collapsed" desc="HttpServlet methods. Click on the + sign on the left to edit the code.">
    /** 
     * Handles the HTTP <code>GET</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    } 

    /** 
     * Handles the HTTP <code>POST</code> method.
     * @param request servlet request
     * @param response servlet response
     * @throws ServletException if a servlet-specific error occurs
     * @throws IOException if an I/O error occurs
     */
    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
    throws ServletException, IOException {
        processRequest(request, response);
    }

    /** 
     * Returns a short description of the servlet.
     * @return a String containing servlet description
     */
    @Override
    public String getServletInfo() {
        return "Short description";
    }// </editor-fold>

}
